1_
Read all persons
C# using (EntitiesModel dbContext = new EntitiesModel()) { foreach (var person in dbContext.Persons) { string firstName = person.FirstName; } }
2_
Add new person
C# using (EntitiesModel dbContext = new EntitiesModel()) { Persons newPerson = new Persons(); newPerson.FirstName = "New"; dbContext.Add(newPerson); dbContext.SaveChanges(); }
3_
Update Person with Id 1
C# using (EntitiesModel dbContext = new EntitiesModel()) { Persons person = dbContext.Persons.Where(p => p.Id==1).FirstOrDefault(); person.LastName += "++"; dbContext.SaveChanges(); }
4_
Delete persons with Id > 1
C# using (EntitiesModel dbContext = new EntitiesModel()) { IQueryable<Persons> personsToDelete = dbContext.Persons.Where(p => p.Id > 1); dbContext.Delete(personsToDelete); // dbContext.Delete(personsToDelete.FirstOrDefault()); One record can be deleted as well dbContext.SaveChanges(Telerik.OpenAccess.ConcurrencyConflictsProcessingMode.AggregateAll); }
5_
Concurency handling during save
C# // executes all SQL statements regardless of error dbContext.SaveChanges(Telerik.OpenAccess.ConcurrencyConflictsProcessingMode.AggregateAll); // stops the processing after the first error occurs dbContext.SaveChanges(Telerik.OpenAccess.ConcurrencyConflictsProcessingMode.StopOnFirst);
6_
Automatic join with WHERE (persons and departments)
Normally it possibl eto write join between 2 tables like below
C# var people2 = dbContext.Persons .Join( dbContext.Departments, p => p.Department_id, d => d.Id, (p, d) => new { name = p.FirstName, name1 = p.LastName, d.Name } );Telerik offers another approach without JOIN clause
SQL var people3 = from p in dbContext.Persons from d in dbContext.Departments where p.Department_id == d.Id // works like JOIN select new { p.FirstName, p.LastName, d.Name };The syntax above will generate a SQL Statement with CROSS JOIN (which with WHERE clause will behave like INNER JOIN)
SQL SELECT a.[firstName] AS COL1, a.[lastName] AS COL2, b.[name] AS COL3 FROM [MY_SCHEMA].[persons] a CROSS JOIN [MY_SCHEMA].[departments] AS b WHERE a.[department_id] = b.[id]
7_
Use navigation property to read Department name for every person
It is possible to use Navigation to access data in related tables
1. If DepartmentId is null then person.Departments.Name will throw an exception
2. Telerik will generate one SQL statement to retrieve Person data and one SQL statement for every unique department ID.
If more people have the same Department_ID then Telerik will generate only one statement for the ID as an argument for a stored procedure.
C# foreach (Persons person in dbContext.Persons) { string departmenName = person.Departments.Name; }It is necessary to be aware:
1. If DepartmentId is null then person.Departments.Name will throw an exception
2. Telerik will generate one SQL statement to retrieve Person data and one SQL statement for every unique department ID.
If more people have the same Department_ID then Telerik will generate only one statement for the ID as an argument for a stored procedure.
SQL SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7 FROM [MY_SCHEMA].[persons] a ORDER BY COL1 declare @p1 int set @p1=2 exec sp_prepexec @p1 output,N'@p0 int',N'SELECT [head] AS COL1, [name] AS COL2 FROM [MY_SCHEMA].[departments] WHERE [id] = @p0',@p0=1 select @p1 declare @p1 int set @p1=2 exec sp_prepexec @p1 output,N'@p0 int',N'SELECT [head] AS COL1, [name] AS COL2 FROM [MY_SCHEMA].[departments] WHERE [id] = @p0',@p0=2 select @p1
8_
Join with in memory list and data in DB (person and departments ids)
Telerik enables writing queries that join data in memeory with data in SQL database
C# var persons5 = (from p in dbContext.Persons where p.FirstName == ("John") join d in deptIds on p.Department_id equals d select p) .ToList();As you can see join happens in memory. Generated SQL statement:
SQL SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7 FROM [MY_SCHEMA].[persons] a WHERE a.[firstName] = 'John' ORDER BY COL1
9_
How will SQL query look like when WHERE in before or after JOIN)
The example above shows that WHERE clause was present. It is the case only when WHERE clause
was present before before JOIN.
C# List<int> deptIds = new List<int> { 1, 2 }; // user selects ids in UI var persons6 = (from p in dbContext.Persons join d in deptIds on p.Department_id equals d where p.FirstName == ("John") select p) .ToList();When WHERE is after JOIN, it will be generated in SQL statement, which will cause performance problems with large data.
SQL SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7 FROM [MY_SCHEMA].[persons] a ORDER BY COL1
10_
When will ORDER BY (unexpectedly) present in SQL query
C# var persons12 = (from p in dbContext.Persons select p ).ToList();ORDER BY clause could cause perfromance problems with large data when for example data is queried from a View and telerik picks a column that does not have an index.
SQL SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7 FROM [MY_SCHEMA].[persons] a ORDER BY COL1If instead of full table specific column is specified
C# var persons13 = (from p in dbContext.Persons select new { p.LastName } ).ToList();then ORDER BY clause disaperars.
SQL SELECT a.[lastName] AS COL1 FROM [MY_SCHEMA].[persons] aIf multiple columns are selected, ORDER BY is not present either.
C# var persons14 = (from p in dbContext.Persons select new { p.FirstName, p.LastName } ).ToList();
SQL SELECT a.[firstName] AS COL1, a.[lastName] AS COL2 FROM [MY_SCHEMA].[persons] aIf ORDER BY clause is added then Telerik will enearte SQL respecting that clause and still adding autodetected column.
C# var persons15 = (from p in dbContext.Persons orderby p.LastName select p ).ToList();
SQL SELECT a.[id] AS COL1, a.[birthday] AS COL2, a.[department_id] AS COL3, a.[department_id] AS COL4, a.[firstName] AS COL5, a.[height] AS COL6, a.[lastName] AS COL7 FROM [MY_SCHEMA].[persons] a ORDER BY COL7, COL1
11_
How will be join tables for N to relationship in model handled
If you have two tables with n to n relationship like
As result of that behavior, you need to write data that would normally required writing a JOIN, you need to take a different approach. For example, if you want to read all employees who belong to manager role you need to write query like this
SQL CREATE TABLE [MY_SCHEMA].[persons]( [id] [int] IDENTITY(1,1) NOT NULL, [firstName] [varchar](20) NULL, [lastName] [varchar](20) NOT NULL, ) CREATE TABLE [MY_SCHEMA].[roles]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](50) NULL, -- e.g. Manager, Engineer, Accountant [Description] [nvarchar](max) NULL, )and so called joined table that defines the relationship
SQL CREATE TABLE [MY_SCHEMA].[persons_roles]( [Person_ID] [int] NOT NULL, [Role_ID] [int] NOT NULL )then Telerik designer will NOT generate Entity for the helper table persons_roles. Instead Telerik will generate navigation property Roles in Person(s) class
As result of that behavior, you need to write data that would normally required writing a JOIN, you need to take a different approach. For example, if you want to read all employees who belong to manager role you need to write query like this
C# var personsInRole = dbContext.Persons .Where(p => p.Roles.Where(role => role.Name == "Manager").Count() > 0 );If you need the helper table to be presented in data model, then if you add a dummy column to the table then Telerik will generate Entity for the table. It of course mean, that query join queries will not work anymore, because the naviagtion property disappears. You need to write standard joins.